﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using InsideDL.MISService;
using InsideDL.MISControl;
using InsideDL.MISData;
using System.IO;

namespace InsideDL
{
    public partial class frmIG001 : MISControl.frmMatrix
    {
        protected EXLAPP exlAPP = null;
        string[] thisMonCells = null;
        string[] lastMonCells = { "", "", "" };

        public frmIG001()
        {
            InitializeComponent();

            Utility.LoadConfigValue("frmIG001");
            
            this.controlList = new List<ucSelFileOrPath>
            {
                ucThisMonth,ucUpdFile,ucBalance
            };
            ucExecute1.ExecuteClick += new EventHandler(ucClickFacade);
            ucExecute_Exit.ExecuteClick += new EventHandler(ucClickFacade);
        }

        protected override void execute()
        {
            lstResult.Items.Clear();
            if (base.Vaildation().Equals(false))             
            { return; }

            string SourceSht, TargetSht, fromRng,fromRng2,toRng;
            

            SourceSht = Utility.GetConfigValue("SourceSht");
            TargetSht = Utility.GetConfigValue("TargetSht");
            /*
            fromRng = Utility.GetNewRange(Utility.GetConfigValue("SourceCol"),
                Utility.GetConfigValue("SouceRow"));
            fromRng2 = Utility.GetNewRange(Utility.GetConfigValue("SourceCol"),
                Utility.GetConfigValue("LastRow"));
            toRng = Utility.GetNewRange(Utility.GetConfigValue("TargetCol"),
                Utility.GetConfigValue("TargetRow"));
            */
            fromRng = Utility.GetNewRange(Utility.GetConfigValue("SourceCol"),
                Utility.GetConfigValue("SouceRow"), ucThisMonth.Txt,'_');
            fromRng2 = Utility.GetNewRange(Utility.GetConfigValue("SourceCol"),
                Utility.GetConfigValue("LastRow"), ucThisMonth.Txt, '_');
            toRng = Utility.GetNewRange(Utility.GetConfigValue("TargetCol"),
                Utility.GetConfigValue("TargetRow"), ucThisMonth.Txt, '_');

            thisMonCells = Utility.GetConfigValue("VerifyRow")
                .Split(new[] {',' });
            char[] chs = Utility.GetConfigValue("SourceCol").ToCharArray();
            string nrng = Convert.ToChar(Convert.ToInt16(chs[0])
                + 13).ToString();
            for (int i = 0; i < thisMonCells.Length; i++)
            {
                thisMonCells[i] = string.Format("{0}{1}", toRng.Substring(0, 1)
                    , thisMonCells[i]);
                lastMonCells[i] = string.Format("{0}{1}", nrng.Substring(0, 1)
                    , thisMonCells[i].Substring(1));
            }

            
            exlAPP = new EXLAPP();
            exlAPP.OpenFile(ucUpdFile.Txt);
            exlAPP.OpenFile(ucThisMonth.Txt);
            
            exlAPP.CopyPaste(SourceSht, string.Format("{0}:{1}", fromRng , fromRng2)
                , TargetSht, toRng);
            exlAPP.SaveCloseXLS();
            exlAPP = new EXLAPP();
            exlAPP.OpenFile(ucUpdFile.Txt);
            for (int i = 0; i < thisMonCells.Length; i++)
            {
                thisMonCells[i] = exlAPP.GetSheetRangeValue(TargetSht
                    , thisMonCells[i]);
                lastMonCells[i] = exlAPP.GetSheetRangeValue(TargetSht
                    , lastMonCells[i]);
            }
            verifyNumeric();
            //exlAPP.SaveCloseXLS();
            verifyNumeric2();
            showMessage(this.Text, "完成！");
        }

        /// <summary>
        /// 檢查數字是否有符合
        /// </summary>
        protected void verifyNumeric()
        {
            //1.先確認自結資產負債表及損益表中的數字是否正確
            //exlAPP.OpenFile(ucUpdFile.Txt);
            exlAPP.OpenFile(ucBalance.Txt);
            //BalanceSht,BalanceCell=D7,D8,D9 → 對應到當月的 278 284 285
            //,D43,D44,D45 → 對應到12月的278 284 285
            string balanceSht =Utility.GetConfigValue("BalanceSht");
            string[] balanceCells = Utility.GetConfigValue("BalanceCell")
                .Split(new char[] { ',' });
            string[] balanceCellValues = Utility.GetConfigValue("BalanceCell")
                            .Split(new char[] { ',' });

            //把值填入
            for (int i = 0; i < balanceCells.Length; i++)
            {
                balanceCellValues[i] = exlAPP.GetSheetRangeValue(balanceSht
                    , balanceCellValues[i]);
            }
            string errStr = string.Empty;
            for (int i = 0; i < thisMonCells.Length; i++)
            {
                if (!thisMonCells[i].ToString().Equals(
                                        balanceCellValues[i].ToString()))
                {
                    writeErr(Path.GetFileName(ucBalance.Txt), balanceSht
                        , balanceCells[i]);
                }
                if (!lastMonCells[i].ToString().Equals(
                                                        balanceCellValues[i+3].ToString()))
                {
                    writeErr(Path.GetFileName(ucBalance.Txt), balanceSht
                        , balanceCells[i + 3]);
                }
            }
        }

        /// <summary>
        /// 檢查YYYYMM繳別達成率-MIS.xls 和每月更新資料-FYP.xls 
        /// 278、284、285 這三個資料是否相等
        /// </summary>
        protected void verifyNumeric2()
        {
            exlAPP.OpenFile(ucUpdFile.Txt);
            exlAPP.OpenFile(ucThisMonth.Txt);
            //比較12個月
            string rng = string.Empty;
            string[] rows = Utility.GetConfigValue("VerifyRow").Split(new char[]{','});
            string v1, v2,sourceSht,targetSht,sourceCol;
            sourceSht = Utility.GetConfigValue("SourceSht");
            targetSht = Utility.GetConfigValue("TargetSht");
            sourceCol = Utility.GetConfigValue("SourceCol");
            for (int i = 1; i <= 12; i++)
            {
                foreach(string row in rows)
                {
                    rng = Utility.GetNewRange(sourceCol , row , i);
                    v1 = exlAPP.GetSheetRangeValue(targetSht , rng);
                    v2 = exlAPP.GetSheetRangeValue(sourceSht , rng);
                    if (!v1.Equals(v2))
                    {
                        writeErr(Path.GetFileName(ucThisMonth.Txt), sourceSht
                            , rng);
                    }
                }
            }
            //要檢查最後Q欄的加總是否正確
            int iSouceRow, iLastRow;
            iSouceRow = Convert.ToInt16(Utility.GetConfigValue("SouceRow"));
            iLastRow = Convert.ToInt16(Utility.GetConfigValue("LastRow"));
            for (int i = iSouceRow; i <= iLastRow; i++)
            {                
                rng = Utility.GetNewRange(sourceCol, i.ToString(), 13);
                v1 = exlAPP.GetSheetRangeValue(targetSht, rng);
                v2 = exlAPP.GetSheetRangeValue(sourceSht, rng);
                if (!v1.Equals(v2))
                {
                    writeErr(Path.GetFileName(ucThisMonth.Txt), sourceSht
                        , rng);
                }              
            }
            
        }

        protected void writeErr(string p_fle, string p_sht, string p_cell)
        {
            lstResult.Items.Add(string.Format("錯誤：{0}→{1}→{2}",
                p_fle, p_sht,p_cell));
        }
    }
}
